<?php

class Model_Event extends PhalApi_Model_NotORM
{

    public function listEvents()
    {
        /* $sql1 = 'SELECT t.id, t.Name, t.Hospital,t.Depart,v.Status,v.Location '
             . 'FROM Event AS e LEFT JOIN Doctor_Info AS d '
             . 'ON e.id = d.id '
             . 'LEFT JOIN Patient AS p '
             .'ON e.Pid = p.Pid ';*/
        /* $rs=$this->getORM()
            ->select(
                'Event.id','Event.Illness','Event.Illness_info','Event.CreateDate',
                'Event.Doctor_id','Doctor.Name as Doctor_Name',
                'Event.Location_id','Location.Address',
                'Event.Patient_id','Patient.Name as Patient_Name'
            )
            ->fetchRows();
        $sql = 'SELECT  E.id,E.Illness,E.Illness_info,E.CreateDate, D.id AS Doctor_id,D.Name as Doctor_Name, E.Location_id,E.Pid,E.Name as Patient_Name'
            . 'FROM (select Event.id AS id,P.id AS Pid,Illness_info,Illness,CreateDate,Status,Location_id,Name,PhoneNumber
              FROM Event
              LEFT JOIN Patient As P ON Event.id=P.id)'
            . 'As E'
            . 'LEFT JOIN EventAndDoctor As ED ON E.id=ED.Event_id'
            . 'LEFT JOIN Doctor AS D ON ED.Doctor_id=D.id;';*/
//        $sql = 'SELECT  E.id AS EventId, E.Illness,E.Illness_info,E.CreateDate,D.id AS DoctorId,D.Name as Doctor_Name, E.Location_id,E.Pid As PatientId, E.PatientName as PatientName ,
//E.Name As Name,E.Callphone,E.Status As EventStatus,E.Address AS EventLocation,E.Lng,E.Lat
//FROM (
// SELECT Event.id AS id,P.id AS Pid,P.Name As PatientName,Illness_info,Illness,CreateDate,Status,Location_id,Event.Name,PhoneNumber,CallPhone,L.Address AS Address ,L.lng AS Lng,L.lat AS Lat
//      FROM Event
//        LEFT JOIN Patient As P ON Event.Patient_id=P.id
//        LEFT JOIN Location As L ON Event.Location_id=L.id
//)As E
//LEFT JOIN Dispatch As ED ON E.id=ED.Event_id
//LEFT JOIN Doctor AS D ON ED.Doctor_id=D.id;
//';
        $sql = 'SELECT  E.id AS EventId, E.Illness,E.Illness_info,E.CreateDate, E.Location_id,E.Pid As PatientId, E.PatientName as PatientName ,
E.Name As CallName,E.CallPhone,E.Status As EventStatus,E.Address AS EventLocation,E.Lng,E.Lat,E.Title
FROM (
 SELECT Event.id AS id,P.id AS Pid,P.Name As PatientName,Illness_info,Illness,CreateDate,Status,Location_id,Event.Name,PhoneNumber,CallPhone,L.Address AS Address ,L.lng AS Lng,L.lat AS Lat,Title
      FROM Event
        LEFT JOIN Patient As P ON Event.Patient_id=P.id
        LEFT JOIN Location As L ON Event.Location_id=L.id
)As E
ORDER BY E.CreateDate DESC
';
        $rs = $this->getORM()->queryAll($sql, array());

        return $rs;
    }

    public function getEventsOfDoctor($doctor_id)
    {
        $sql='
              SELECT  D.id AS DoctorId,D.Name as DoctorName, E.id AS EventId
              FROM Doctor AS D
                LEFT JOIN Dispatch As ED ON D.id=ED.Doctor_id
                LEFT JOIN Event AS E ON  E.id=ED.Event_id
              WHERE D.id=:Did';
        $params = array(':Did' => $doctor_id);;
        return $this->getORM()
            ->queryAll($sql, $params);
    }

    public function getEventInfo($event_id)
    {
        return $this->getORM()
            ->select( 'Event.id as EventId','Event.Illness','Event.Title as Title','Event.CreateDate','Event.Status','Event.Location',
                'Event.Location_id','Location.Address',
                'Event.Patient_id as PatientId','Patient.Name as PatientName',
                'Event.Name as CallName','Event.CallPhone','Location.Lng as Lng','Location.Lat as Lat'

            )
            ->where(array('Event.id'=>$event_id))
            ->fetch();


    }

    protected function getTableName($id)
    {
        return 'Event';
    }

    protected function getTableKey($table)
    {
        return 'id';
    }
}